﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Power.Model;

namespace Power.DAL
{
    public class UserDal
    {
        public void DeleteById(int id)
        {
            //软删除
            SqlHelper.ExecuteNonQuery("Update T_User Set IsDeleted=1 where Id=@Id",
                new SqlParameter("@Id", id));
        }

        public User[] ListAll()
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_User where IsDeleted=0");
            User[] Users = new User[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Users[i] = ToUser(dt.Rows[i]);
            }
            return Users;
        }



        public User[] ListToAct()
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_User where IsDeleted=0 and Usertype =0");
            User[] Users = new User[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Users[i] = ToUser(dt.Rows[i]);
            }
            return Users;
        }


        public User[] ListToFind(string sttext)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_User where IsDeleted=0 and( Username= @Temppar or EmployeeID=@Temppar)", new SqlParameter("@Temppar", sttext));
            User[] Users = new User[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Users[i] = ToUser(dt.Rows[i]);
            }
            return Users;
        }







        public void Insert(User op)
        {
            //bit类型，在sql语句中要写0、1
            //在.net中要用bool表示
            SqlHelper.ExecuteNonQuery(@"insert into T_User(
                UserName,Password,IsDeleted) values(@UserName,@Password,0)",
                    new SqlParameter("@UserName", op.UserName),
                    new SqlParameter("@Password", op.Password));
        }
        public void InsertUser(User op)
        {
            //bit类型，在sql语句中要写0、1
            //在.net中要用bool表示
            SqlHelper.ExecuteNonQuery(@"insert into T_User(
                UserName,Password,IsDeleted,Usertype) values(@UserName,@Password,0,3)",
                    new SqlParameter("@UserName", op.UserName),
                    new SqlParameter("@Password", op.Password));
        }




        public void Insertnew(User op)
        {
            //bit类型，在sql语句中要写0、1
            //在.net中要用bool表示
            SqlHelper.ExecuteNonQuery(@"insert into T_User(
                UserName,Password,EmployeeID,IsDeleted,Usertype) values(@UserName,@Password,@EmployeeID,0,0)",
                    new SqlParameter("@UserName", op.UserName),
                    new SqlParameter("@Password", op.Password),
                    new SqlParameter("@EmployeeID", op.EmployeeID));
        }

        public void InsertnUser(User op)
        {
            //bit类型，在sql语句中要写0、1
            //在.net中要用bool表示
            SqlHelper.ExecuteNonQuery(@"insert into T_User(
                UserName,Password,EmployeeID,IsDeleted,Usertype) values(@UserName,@Password,@EmployeeID,0,3)",
                    new SqlParameter("@UserName", op.UserName),
                    new SqlParameter("@Password", op.Password),
                    new SqlParameter("@EmployeeID", op.EmployeeID));
        }


        public void InsertvUser(User op)
        {
            //bit类型，在sql语句中要写0、1
            //在.net中要用bool表示
            SqlHelper.ExecuteNonQuery(@"insert into T_User(
                UserName,Password,EmployeeID,IsDeleted,Usertype) values(@UserName,@Password,@EmployeeID,0,@Usertype)",
                    new SqlParameter("@UserName", op.UserName),
                    new SqlParameter("@Password", op.Password),
                    new SqlParameter("@EmployeeID", op.EmployeeID)                    ,
                    new SqlParameter("@Usertype", op.Usertype)
                    );
        }



        private User ToUser(DataRow row)
        {
            User op = new User();
            op.Id = (int)row["Id"];
            op.UserName = (string)row["UserName"];
            op.Password = (string)row["Password"];
            op.Usertype = (int)row["Usertype"];
            op.IsDeleted = (bool)row["IsDeleted"];
            op.EmployeeID = (string)row["EmployeeID"];

            return op;
        }


        /// <summary>
        /// 更新密码
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="realName"></param>
        /// <param name="pwd"></param>
        public void Update(int id, string userName, string realName, string pwd)
        {
            SqlHelper.ExecuteNonQuery("update T_User set UserName=@UserName,Password=@Password where Id=@Id",
               new SqlParameter("@UserName", userName),
               new SqlParameter("@Password", pwd),
               new SqlParameter("@Id", id));
        }


        public void RootUpdate(string userName, string password, string emid)
        {
            SqlHelper.ExecuteNonQuery("update T_User set Password=@Password,EmployeeID=@EmployeeID where UserName=@UserName",
               new SqlParameter("@UserName", userName),
               new SqlParameter("@EmployeeID", emid),
               new SqlParameter("@Password", password));
        }


        public void RootUpdatepass(string userName, string password)
        {
            SqlHelper.ExecuteNonQuery("update T_User set Password=@Password where UserName=@UserName",
               new SqlParameter("@UserName", userName),
               new SqlParameter("@Password", password));
        }

        public void UserUpdate(string userName, string password)
        {
            SqlHelper.ExecuteNonQuery("update T_User set Password=@Password where UserName=@UserName",
               new SqlParameter("@UserName", userName),
               
               new SqlParameter("@Password", password));
        }


        public void RootAct(string userName)
        {
            SqlHelper.ExecuteNonQuery("update T_User set Usertype=3 where UserName=@UserName",
               new SqlParameter("@UserName", userName));
        }

        public void RootActE(string userName)
        {
            SqlHelper.ExecuteNonQuery("update T_User set Usertype=4 where UserName=@UserName",
               new SqlParameter("@UserName", userName));
        }

        public void RootActAll()
        {
            SqlHelper.ExecuteNonQuery("update T_User set Usertype=3 where Usertype=0 and IsDeleted=0");
        }


        public void RootDel(string userName)
        {
            SqlHelper.ExecuteNonQuery("update T_User set IsDeleted=1 where UserName=@UserName",
               new SqlParameter("@UserName", userName));
        }



        public User GetById(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where Id=@Id and IsDeleted=0",
                new SqlParameter("@Id", id));
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            else if (table.Rows.Count > 1)
            {
                throw new Exception("存在Id重复用户！");
            }
            else
            {
                return ToUser(table.Rows[0]);
            }
        }

        public User GetByUserName(string userName)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where UserName=@UserName and IsDeleted=0 and Usertype>2",
                new SqlParameter("@UserName", userName));
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            else if (table.Rows.Count > 1)
            {
                throw new Exception("存在重名");
            }
            else
            {
                return ToUser(table.Rows[0]);
            }
        }

        public bool JudgeByUserName(string userName)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where UserName=@UserName and IsDeleted=0",
                new SqlParameter("@UserName", userName));
            if (table.Rows.Count == 0)
            {
                return false;
            }

            else
            {
                return true;
            }
        }







        public int GetNametype(string userName)
        {
            int temppars=3;
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where UserName=@UserName ",
                new SqlParameter("@UserName", userName));
                        User Users = new User();

                Users = ToUser(table.Rows[0]);
            temppars=Users.Usertype;

            return temppars;

        }


        public User GetByRootName(string userName)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_User where UserName=@UserName and IsDeleted=0 and Usertype>3 ",
                new SqlParameter("@UserName", userName));
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            else if (table.Rows.Count > 1)
            {
                throw new Exception("存在重名");
            }
            else
            {
                return ToUser(table.Rows[0]);
            }
        }

   }
}
